package dao;

import dto.Account;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

public class AccountDAO extends AbstractDAO {

    private String insert = "insert into Account (firstname, lastname, email, password) VALUES (?, ?, ?, ?)";

    public boolean addAccount(Account ac) {
        PreparedStatement ps;
        try {
            ps = (PreparedStatement) connection.prepareStatement("insert into account(firstname,lastname,email,password,gender,country) values (?,?,?,?,?,?)");
            ps.setString(1, ac.getFirstName());
            ps.setString(2, ac.getLastName());
            ps.setString(3, ac.getEmail());
            ps.setString(4, ac.getPassword());
            ps.setString(5, ac.getGender());
            ps.setString(6, ac.getCountry());
            ps.execute();
        } catch (SQLException ex) {
            ex.printStackTrace();
            Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE,
                    null, ex);
            return false;
        }
        return true;
    }

    public void checkRegisteredAccount(Account ac) throws Exception {
        PreparedStatement ps, pstmt;
        try {
            System.out.println("Inside checkRegisteredAccount");
            String eMail = ac.getEmail();
            System.out.println("Email" + eMail);

            String password = ac.getPassword();
            System.out.println("password" + password);
            String searchQuery = "SELECT * FROM account WHERE email LIKE'"
                    + eMail + "' and password LIKE '" + password + "'";
            String searchMailQuery = "SELECT * FROM account WHERE email LIKE'"
                    + eMail + "'";
            String searchPasswdQuery = "SELECT * FROM account WHERE password LIKE '"
                    + password + "'";
            ps = connection.prepareStatement(searchMailQuery);
            ResultSet rs = ps.executeQuery();
            pstmt = connection.prepareStatement(searchPasswdQuery);
            ResultSet rs2 = pstmt.executeQuery();
            if (!rs.first()) {
                throw new Exception("invalidMail");
            }
            if (!rs2.first()) {
                throw new Exception("invalidpasswd");

            } else {
                ps = connection.prepareStatement(searchQuery);
                ResultSet rs3 = ps.executeQuery();
                while (rs3.next()) {
                    ac.setFirstName(rs3.getString(2));
                    ac.setLastName(rs3.getString(3));
                    ac.setEmail(rs3.getString(4));
                    ac.setPassword(rs3.getString(5));
                    ac.setStatusMsg(rs3.getString(6));
                    ac.setGender(rs3.getString(7));
                    ac.setCountry(rs3.getString(8));
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
        }							// contact list
    }

    public boolean checkExistingAccout(Account ac) {
        PreparedStatement ps;
        boolean isAccountExist = false;
        try {
            String eMail = ac.getEmail();
            System.out.println(eMail);
            String likeClause = eMail;
            String searchquery = "SELECT * FROM account WHERE email LIKE'" + likeClause + "'";
            ps = connection.prepareStatement(searchquery);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString(1));
            }
            if (rs.first()) {
                isAccountExist = true;
            }

        } catch (SQLException ex) {
            Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
        }


        return isAccountExist;

    }
    /**
     * Return account 's details according to mail
     */
    public Account getAccount(String mail) {
        Account account = null;

        try {
            PreparedStatement ps;

            ps = connection
                    .prepareStatement("select * from account where email = ?");
            ps.setString(1, mail);
            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                account = new Account();
                account.setFirstName(rs.getString("firstname"));
                account.setLastName(rs.getString("lastname"));
                account.setEmail(rs.getString("email"));
                account.setStatusMsg(rs.getString("status_msg"));
                account.setGender(rs.getString("gender"));
                account.setCountry(rs.getString("country"));
            }

        } catch (SQLException ex) {
            Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE,
                    null, ex);
        }

        return account;

    }
    /**
     * Return account 's details according to ID.
     */
    public Account getAccount(int id) {
        Account account = null;

        try {
            PreparedStatement ps;

            ps = connection
                    .prepareStatement("select * from account where id= ?");
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                account = new Account();
                account.setFirstName(rs.getString("firstname"));
                account.setLastName(rs.getString("lastname"));
                account.setEmail(rs.getString("email"));
                account.setStatusMsg(rs.getString("status_msg"));
                account.setGender(rs.getString("gender"));
                account.setCountry(rs.getString("country"));
            }

        } catch (SQLException ex) {
            Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE,
                    null, ex);
        }

        return account;

    }
    
    public  Vector<Vector> getAllAccounts()
    {
       ////////////////////////////////////////
        Vector<Vector> testing = new Vector<Vector>();
       Vector<Account> AllAccounts = null;

        try {
            Statement stmnt=connection.createStatement();
            ResultSet rs = stmnt .executeQuery("select * from account");

            if (rs.next()) {
                Account account = new Account();
                account.setFirstName(rs.getString("firstname"));
                account.setLastName(rs.getString("lastname"));
                account.setEmail(rs.getString("email"));
                account.setStatusMsg(rs.getString("status_msg"));
                account.setGender(rs.getString("gender"));
                account.setCountry(rs.getString("country"));
            AllAccounts.add(account);
            }
            
            testing.add(AllAccounts);

        } catch (SQLException ex) {
            Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE,
                    null, ex);
        }

        return testing;

        ////////////////////////////////////////
        
    }
    /**
     * Return the ID of an account.
     */
    public int getAccountId(String Email) {
        int contactId = 0;
        try {

            PreparedStatement ps = connection.prepareStatement("select id from account where email=?");
            ps.setString(1, Email);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                contactId = rs.getInt("id");
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return contactId;
    }

    public int getCount() throws SQLException {
        PreparedStatement stmt = connection.prepareStatement("Select count(*) from Account");
        ResultSet set = stmt.executeQuery();
        set.next();
        return set.getInt(1);        
    }
}
